import openpyxl
client_list = []

#每个客户的数据结构
# client = {
#     'name':'TBD',
#     'date':'TBD',
#     'total':'0.00',
#     'summary':'TBD',
#     'nav':[],
#     'holdings':[],
#     'allocation':[],
#     'comment':''
# }

def load(file):
    excel = openpyxl.load_workbook(file)
    for ws in excel:
        client = {}
        client['name'] = client_name(ws)
        client['date'] = report_date(ws)
        client['navs'] = nav(ws)
        client['holdings'] = holdings(ws)   
        client['alloc'] = alloc(ws)       
        client['total'] = total(ws)     
        client['summary'] = summary(ws)       
        client['comment'] = comment(ws)       
        client_list.append(client)

def client_name(ws):
    return ws['B1'].value

def report_date(ws):
    return ws['B2'].value

def nav(ws):
    navs = []
    for row in ws.iter_rows(min_row=7, max_row=14):
        date = row[0].value
        nav =  row[1].value
        change = row[2].value
        if len(navs) > 0:
            change = float(nav) - float(navs[-1][1])
        navs.append((date, nav, change))
    return navs
        

def holdings(ws):
    start_index = 0
    for index, row in enumerate(ws.rows):
        if row[0].value == '十大重仓股':
            start_index = index + 3
            break 
    holdings = []
    for row in ws.iter_rows(min_row=start_index):
        if row[0].value == None or row[0].value.strip == '':
            break
        holdings.append((row[0].value, row[1].value, row[2].value))
    return holdings

def alloc(ws):
    start_index = 0
    for index, row in enumerate(ws.rows):
        if row[0].value == '投资组合':
            start_index = index + 2
            break 
    alloc = []
    for row in ws.iter_rows(min_row=start_index):
        if row[0].value == None or row[0].value.strip == '':
            break
        alloc.append((row[0].value, row[1].value))
    return alloc

def total(ws):
    return ws['B3'].value

def summary(ws):
    return ws['B4'].value

def comment(ws):
    for index, row in enumerate(ws.rows):
        if row[0].value == '市场评论':
            return row[1].value

if __name__ == '__main__':
    load('data.xlsx')
    print(client_list)